﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_MySendMail]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_MySendMail];
GO
CREATE PROCEDURE [dbo].[sproc_MySendMail] 
    @ClassID int,
    @Receiver nvarchar(255),
    @CopyReceiver nvarchar(255),
    @FurtiveCopyReceiver nvarchar(255),
    @Sender nvarchar(255),
    @Title nvarchar(255),
    @Content ntext,
    @Attach bit = 1,
    @SendToMe bit = 1
/*

=============================================================================
功能:    发送邮件
参数:
    @ClassID int                :    邮件所在组（类）
    @Receiver nvarchar(255),        :    邮件的接收者
    @CopyReceiver nvarchar(255),        :    邮件的抄送者
    @FurtiveCopyReceiver nvarchar(255),    :    邮件的密送者
    @Sender nvarchar(255),            :    邮件的发送者
    @Title nvarchar(255),            :    标题
    @Content ntext,                :    正文
    @Attach bit = 1,                :    是否带附件
    @SendToMe bit = 1            :    发送完成后是否留备份
备注:
    只负责正文数据，无附件，主要考虑到附件不确定
=============================================================================

*/

As
set nocount on
declare @sql nvarchar(255)
declare @Users nvarchar(2000)
declare @UserName nvarchar(255)
create table #tmpUser (ReceiveName nvarchar(255))

set @Users =@Receiver + ',' + @CopyReceiver + ',' +  @FurtiveCopyReceiver + ','
WHILE charindex(',',@Users)>0
BEGIN
    set @UserName = substring(@users,1,charindex(',',@Users)-1)
    set @Users = substring(@users,charindex(',',@Users)+1,len(@users))
    insert into #tmpUser values(@UserName)
END
--得到唯一的有效的ID
create table #tmpUniqueUser (ReceiveName nvarchar(255))
insert into #tmpUniqueUser select distinct ReceiveName  from #tmpUser where ReceiveName in (select staff_name from uds_staff)

--给每个ID发一封邮件，并给此记录做一个标记，标识是此次发的邮件,一个是@@SPID，一个是文件属性
insert into uds_document (classid,doctitle,doccontent,doctype,docaddedby,docaddeddate,docattribute)select @ClassID,@Title,@Content,1,ReceiveName,getdate(),3 from #tmpUniqueUser

--写邮件的发送人,收件人,以及邮件信息到收件人的收件箱
INSERT INTO UDS_MailAttach 
    SELECT docid,0,@sender,docaddedby,@receiver,@CopyReceiver,@FurtiveCopyReceiver,1 
    FROM uds_document 
        WHERE docsign = @@spid and docattribute =3
    SELECT docid,0,@sender,docaddedby,@receiver,@CopyReceiver,@FurtiveCopyReceiver,1 
    FROM uds_document 
        WHERE docsign = @@spid and docattribute =3

UPDATE uds_document 
    SET docattribute =2, docaddedby = @sender 
    WHERE docsign = @@spid

IF @SendToMe = 1
BEGIN
--给发件人自己发一个邮件
insert into uds_document (classid,doctitle,doccontent,doctype,docaddedby,docaddeddate,docattribute) values(@ClassID,@Title,@Content,1,@Sender,getdate(),1)
--写邮件的发送人,收件人,以及邮件信息到发送人的发件箱
insert into UDS_MailAttach select docid,0,@sender,docaddedby,@receiver,@CopyReceiver,@FurtiveCopyReceiver,2 from uds_document where docsign = @@spid and docattribute =1
--设置文件属性,为附加附件做标识
END
update uds_document set docattribute =1, docaddedby = @sender where docsign = @@spid
IF  @Attach =0
    EXEC sproc_CloseMailAttachFlag

drop table #tmpUser
drop table #tmpUniqueUser
set nocount off